--View user
--PPersonCode, PPersonLogin, PPassword, PPDisplayName, PCampaign, ICNumber

Declare @PPersonName varchar(50), @PPersonCode int

SET @PPersonName = 'Melvin Rajesh'

Select PSinSales, PPersonCode, PPersonLogin, PPassword, PDisplayName, PCampaign, ICNumber  from tblPersonnelKB  
Where PDisplayName Like '%' + @PPersonName + '%'

select @PPersonCode = PPersonCode  from tblPersonnelKB Where PDisplayName Like '%' + @PPersonName + '%'

--View group by Person
select * from tblUserGroupAccess 
Where PPersonCode = @PPersonCode
--select PPersonCode from tblUserGroupAccess where PPersonCode = 896 And IsEnabled = 1
--select * from tblUserGroupAccessDet Where GroupAccessId = 701
--View level of User


--View user access level
select ugad.GroupAccessID, ugad.GroupDescription, ql.QuizLevelID, ql.QuizLevelName, p.PPersonCode, p.PDisplayName
from tblUserGroupLevelAccess ugla 
Inner Join tblPersonnelKB p On ugla.PPersonCode = p.PPersonCode
Inner Join tblQuizLevel ql On ugla.QuizAccessLevel = ql.QuizLevelID

Inner Join tblUserGroupAccess uga On uga.PPersonCode = ugla.PPersonCode And uga.PCampaign = ugla.PCampaign
Inner Join tblUserGroupAccessDet ugad On  uga.GroupAccessID = ugad.GroupAccessId
Where (p.PPersonCode = @PPersonCode)
--And (ql.QuizLevelID = 0 OR ql.QuizLevelName Like '%krisflyer%')
--And (ugad.GroupAccessID = 0 OR ugad.GroupDescription Like '%trainer%')

--View Quiz access by person level
select 'View Quiz access by person level'
select qm.QuizMasterID, qm.QuizMaster, p.PPersonCode, p.PDisplayName
from tblQuizMaster qm INNER JOIN tblQuizMasterLevelAccess qmla ON qm.QuizMasterID = qmla.QuizMasterID
Inner Join tblUserGroupLevelAccess ugla ON qmla.QuizLevelID = ugla.QuizAccessLevel
Inner Join tblUserGroupAccess uga On uga.PPersonCode = ugla.PPersonCode And uga.PCampaign = ugla.PCampaign
Inner Join tblPersonnelKB p On uga.PPersonCode = p.PPersonCode
Where p.PPersonCode = @PPersonCode 
--OR p.PDisplayName Like '%nadia%'
Group By qm.QuizMasterID, qm.QuizMaster, p.PPersonCode, p.PDisplayName

--View QuizResult last 100 By Person
Select 'View QuizResult last 100 By Person'
Select top 100  * from tblQuizResult Where PPersonCode = @PPersonCode
Order By DateTaken DESC

--View result with quiz level
Select 'View result with quiz level'
Select  ql.QuizLevelID, ql.QuizLevelID, qr.QuizMasterID, qm.QuizMaster, qr.QuizResultID
from tblQuizResult qr 
Inner Join tblQuizMaster qm On qr.QuizMasterID = qm.QuizMasterID
Inner Join tblQuizMasterLevelAccess qmla On qm.QuizMasterID = qmla.QuizMasterID 
Inner Join tblQuizLevel ql ON ql.QuizLevelID = qmla.QuizLevelID
Where PPersonCode = @PPersonCode

Select 'View Passing Marks'
SELECT     qpm.PassingGrad, qpm.PassingRetest
FROM         tblQuizPassingMarks AS qpm INNER JOIN
   tblUserGroupAccess AS uga ON qpm.GroupAccessID = uga.GroupAccessID
WHERE     (uga.PPersonCode = @PPersonCode) AND (uga.PCampaign = 'Krisflyer') AND (uga.isEnabled = 1)

--View Quiz Quit Reason
Select 'View Quiz Quit Reason'
select * from tblQuizExit Where PPersonCode = @PPersonCode